"""
Case Type   : PyDriver+mysql和opengauss兼容B库对比
Create At   : 2024/01/12
Owner       : z30056528
Description :
    1.读取通用SQL脚本
    2.解析sql脚本，生成可执行sql语句
    3.通过PyDriver连接数据库,执行sql
    4.对mysql和opengauss兼容B库结果进行对比
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功
    4.执行成功，结果一致
"""
import mysql.connector
import psycopg2
import py_opengauss
from Driver_Common import ComODBC
from openGaussBase.testcase.utils import Logger
from yat.test import Node
from yat.test import macro


class PyDriver(object):
    def __init__(self):
        self.log = Logger()
        self.comODBC = ComODBC()
        self.mysql_node = Node('mysql')
        self.og_node = Node('og')

    def execute_psycopg2_gauss(self, sql):
        text = '连接数据库, expect:执行成功'
        self.log.info(f'-----{text}-----')
        conn = psycopg2.connect(database=self.og_node.db_name,
                                user=self.og_node.db_user,
                                password=self.og_node.db_password,
                                host=self.og_node.db_host,
                                port=self.og_node.db_port)
        with conn:
            with conn.cursor() as cursor:
                res = self.comODBC.exec_odbc_sql(sql, cursor, 0)
                return res

    def execute_pyog_gauss(self, sql):
        self.og_node = Node('og')
        text = '连接数据库, expect:执行成功'
        self.log.info(f'-----{text}-----')
        og_info = f"pq://{self.og_node.db_user}:{macro.PASSWD_INITIAL}@" \
            f"{self.og_node.db_host}:{self.og_node.db_port}/" \
            f"{self.og_node.db_name}"
        conn = py_opengauss.open(og_info)
        self.log.info(og_info)
        with conn:
            res = self.comODBC.exec_odbc_sql(sql, conn, 1)
            return res

    def execute_py_mysql(self, sql):
        db = mysql.connector.connect(
            host=self.mysql_node.db_host,
            user=self.mysql_node.db_user,
            passwd=macro.PASSWD_INITIAL,
            database=self.mysql_node.db_name,
            charset='utf8'
        )
        with db:
            with db.cursor() as cursor:
                res = self.comODBC.exec_odbc_sql(sql, cursor, 0)
                return res

    def py_diff_mysql_psycopg2(self, sql):
        self.log.info('---------------sql-----------------------')
        og_res = self.execute_psycopg2_gauss(sql)
        mysql_res = self.execute_py_mysql(sql)
        self.log.info('-----------psycopg2-opengauss res---------')
        self.log.info(og_res)
        self.log.info('-----------mysql res---------')
        self.log.info(mysql_res)
        self.log.info('-----对比mysql和opengauss兼容B库执行结果-----')
        diff = self.comODBC.compare_sql_res(mysql_res, og_res,
                                            'mysql', 'psycopg2-openGauss')
        return diff

    def py_diff_mysql_pyog(self, sql):
        self.log.info('---------------sql-----------------------')
        self.log.info(sql)
        pyog_res = self.execute_pyog_gauss(sql)
        mysql_res = self.execute_py_mysql(sql)
        self.log.info('-----------pyog-opengauss res---------')
        self.log.info(pyog_res)
        self.log.info('-----------mysql res---------')
        self.log.info(mysql_res)
        self.log.info('-----对比mysql和opengauss兼容B库执行结果-----')
        diff = self.comODBC.compare_sql_res(mysql_res, pyog_res,
                                            'mysql', 'pyog-openGauss')
        return diff


if __name__ == '__main__':
    driver = PyDriver()
    example = "select * from person"
    driver.py_diff_mysql_psycopg2(example)
